#! /bin/bash

hive  -S -e  "
--==========访问与咨询宽表========
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;

insert into bxg_dws.dws_visitor_daycount;
with tmp as (
select
    "year",
    year_quarter,
    year_month,
    year_month_day,
    year_month_day_hour,
    area,
    origin_channel,
    seo_source,
    from_url,
    session_id,
    sid,
    ip,
    row_number() over(partition by sid,ip,session_id) as ss_rn
from bxg_dwb.dwb_visitor_wide)
SELECT
    -- 维度
    \`year\`,
    year_quarter,
    year_month,
    year_month_day,
    year_month_day_hour,
    area,
    seo_source,
    origin_channel,
    from_url,
    -- 计算指标-
--    sid,--访客量
 --   ip, --总访问ip量
 --   session_id--会话id
 --时间标记
    case when grouping(year_month_day_hour)=0
         then 'hour' --小时
         when grouping(year_month_day)=0
         then 'day' --天
         when grouping(year_month)=0
         then 'month' --月
         when grouping(year_quarter)=0
         then 'quarter' --季度
         when grouping("year")=0
         then 'year' --年
         else 'others'
         end as time_type,
    -- 分组标记
    case when grouping(year_month_day_hour)=0
         then 'hour'
         when grouping (area)=0
         then 'area'
         when grouping (seo_source)=0
         then 'seo_source'
         when grouping (origin_channel)=0
         then 'origin_channel'
         when grouping (from_url)=0
         then 'from_url'
         else 'others'
         end as group_type,

--总访问sid
case when grouping (year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=15
     then sum(if(ss_rn = 1,1,0))
     when grouping (year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=31
     then sum(if(ss_rn = 1,1,0))
     when grouping (area,seo_source,origin_channel,from_url)=7
     then sum(if(ss_rn = 1,1,0))
     when grouping (area,seo_source,origin_channel,from_url)=11
     then sum(if(ss_rn = 1,1,0))
     when grouping (area,seo_source,origin_channel,from_url)=13
     then sum(if(ss_rn = 1,1,0))
     when grouping (area,seo_source,origin_channel,from_url)=14
     then sum(if(ss_rn = 1,1,0))
     end as sid_total,
--总访问IP
case when grouping (year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=15
     then count(distinct ip)
     when grouping (year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=31
     then count(distinct session_id)
     when grouping (area,seo_source,origin_channel,from_url)=7
     then count(distinct ip)
     when grouping (area,seo_source,origin_channel,from_url)=11
     then count(distinct ip)
     when grouping (area,seo_source,origin_channel,from_url)=13
     then count(distinct ip)
     when grouping (area,seo_source,origin_channel,from_url)=14
     then count(distinct ip)
     end as ip_total,
--会话总量
case when grouping (year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=15
     then count(distinct session_id)
     when grouping (year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=31
     then count(distinct session_id)
     when grouping (area,seo_source,origin_channel,from_url)=7
     then count(distinct session_id)
     when grouping (area,seo_source,origin_channel,from_url)=11
     then count(distinct session_id)
     when grouping (area,seo_source,origin_channel,from_url)=13
     then count(distinct session_id)
     when grouping (area,seo_source,origin_channel,from_url)=14
     then count(distinct session_id)
     end as session_id_total
     /*
--总访问sid
case when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=15
     then sum(if(ss_rn = 1,1,0))
     when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=31
     then sum(if(ss_rn = 1,1,0))
     when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=23
     then sum(if(ss_rn = 1,1,0))
     when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=27
     then sum(if(ss_rn = 1,1,0))
     when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=29
     then sum(if(ss_rn = 1,1,0))
     when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=30
     then sum(if(ss_rn = 1,1,0))
     end as sid_total,
--总访问IP
case when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=15
     then count(distinct ip)
     when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=31
     then count(distinct session_id)
     when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=23
     then count(distinct ip)
     when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=27
     then count(distinct ip)
     when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=29
     then count(distinct ip)
     when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=30
     then count(distinct ip)
     end as ip_total,
--会话总量
case when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=15
     then count(distinct session_id)
     when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=31
     then count(distinct session_id)
     when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=23
     then count(distinct session_id)
     when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=27
     then count(distinct session_id)
     when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=29
     then count(distinct session_id)
     when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=30
     then count(distinct session_id)
     end as session_id_total,
     */
/*
    --总访问sid --粒度月
    case when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=77
         then sum(if(ss_rn = 1,1,0))
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=55
         then sum(if(ss_rn = 1,1,0))
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=61
         then sum(if(ss_rn = 1,1,0))
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=59
         then sum(if(ss_rn = 1,1,0))
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=62
         then sum(if(ss_rn = 1,1,0))
         end as session_id_total,
        --总访问IP
    case when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=77
         then count(distinct ip)
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=55
         then count(distinct session_id)
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=61
         then count(distinct ip)
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=59
         then count(distinct ip)
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=62
         then count(distinct ip)
         end as ip_total,
    --会话总量
    case when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=77
         then count(distinct session_id)
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=55
         then count(distinct session_id)
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=61
         then count(distinct session_id)
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=59
         then count(distinct session_id)
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=62
         then count(distinct session_id)
         end as session_id_total,
    --总访问sid --粒度季度
    case when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=127
         then sum(if(ss_rn = 1,1,0))
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=119
         then sum(if(ss_rn = 1,1,0))
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=125
         then sum(if(ss_rn = 1,1,0))
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=123
         then sum(if(ss_rn = 1,1,0))
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=126
         then sum(if(ss_rn = 1,1,0))
         end as session_id_total,
        --总访问IP
    case when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=127
         then count(distinct ip)
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=119
         then count(distinct session_id)
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=125
         then count(distinct ip)
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=123
         then count(distinct ip)
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=126
         then count(distinct ip)
         end as ip_total,
    --会话总量
    case when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=127
         then count(distinct session_id)
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=119
         then count(distinct session_id)
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=125
         then count(distinct session_id)
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=123
         then count(distinct session_id)
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=126
         then count(distinct session_id)
         end as session_id_total,
    --总访问sid --粒度年
    case when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=255
         then sum(if(ss_rn = 1,1,0))
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=247
         then sum(if(ss_rn = 1,1,0))
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=253
         then sum(if(ss_rn = 1,1,0))
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=251
         then sum(if(ss_rn = 1,1,0))
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=254
         then sum(if(ss_rn = 1,1,0))
         end as session_id_total,
        --总访问IP
    case when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=255
         then count(distinct ip)
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=247
         then count(distinct session_id)
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=253
         then count(distinct ip)
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=251
         then count(distinct ip)
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=254
         then count(distinct ip)
         end as ip_total,
    --会话总量
    case when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=255
         then count(distinct session_id)
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=247
         then count(distinct session_id)
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=253
         then count(distinct session_id)
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=251
         then count(distinct session_id)
         when grouping (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour,area,seo_source,origin_channel,from_url)=254
         then count(distinct session_id)
         end as session_id_total
*/
from tmp
group by
    grouping sets
            (
            -- 小时
            (\"year\",year_quarter,year_month,year_month_day,year_month_day_hour), --小时

            -- 天
            (\"year\",year_quarter,year_month,year_month_day), -- 天粒度
            (\"year\",year_quarter,year_month,year_month_day,area), --地区
            (\"year\",year_quarter,year_month,year_month_day,origin_channel), --渠道来源
            (\"year\",year_quarter,year_month,year_month_day,seo_source), --搜索来源
            (\"year\",year_quarter,year_month,year_month_day,from_url),
            -- 月
            (\"year\",year_quarter,year_month), --月
            (\"year\",year_quarter,year_month,area), --地区
            (\"year\",year_quarter,year_month,origin_channel), --渠道来源
            (\"year\",year_quarter,year_month,seo_source), --搜索来源
            (\"year\",year_quarter,year_month,from_url),
            -- 季度
            (\"year\",year_quarter), --全国
            (\"year\",year_quarter,area), --地区
            (\"year\",year_quarter,origin_channel), --渠道来源
            (\"year\",year_quarter,seo_source), --搜索来源
            (\"year\",year_quarter,from_url),
            -- 年
            (\"year\"), --全国
            (\"year\",area), --地区
            (\"year\",origin_channel), --渠道来源
            (\"year\",seo_source), --搜索来源
            (\"year\",from_url)
            );


"